/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Customer;
import model.Memberaccount;

/**
 *
 * @author Thai Ha Cong
 */
public class AuthenticationDAO {

    private Connection con;

    public AuthenticationDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public boolean checkLogin(Memberaccount memberaccount) {
        boolean result = false;

        String sqlst = "SELECT * FROM memberaccount "
                + "WHERE username = ? AND password = ?";
        try {
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, memberaccount.getUsername());
            ps.setString(2, memberaccount.getPassword());

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                result = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public Customer getCustomerFromUsername(String username) {
        Customer result = new Customer();

        String sqlst = "SELECT * FROM customer, memberaccount "
                + "WHERE memberaccount.username = ? "
                + "AND customer.MemberAccount_idMemberAccount = memberaccount.idMemberAccount";
        try {
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, username);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                Integer idMemberAccount = rs.getInt("idMemberAccount");
//                String username = rs.getString("username");
                String password = rs.getString("password");
                Date openTime = rs.getTimestamp("openTime");
                Date closeTime = rs.getTimestamp("closeTime");
                String status = rs.getString("status");

                Memberaccount memberaccount = new Memberaccount(idMemberAccount, username, password, openTime, status);

                Integer idCustomer = rs.getInt("idCustomer");
                String firstName = rs.getString("firstName");
                String lastName = rs.getString("lastName");
                String address = rs.getString("address");
                String phone = rs.getString("phone");
                Date dob = (Date) rs.getDate("dob");
                String email = rs.getString("email");

                Customer customer = new Customer(idCustomer, firstName, lastName, address, phone, dob, email, memberaccount);

                result = customer;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public boolean register(Customer customer) {
        boolean result = false;

        try {
            //insert member account 1st
            Memberaccount memberaccount = customer.getMemeberaccount();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            
            String sqlst1 = "INSERT INTO memberaccount(username, password, openTime, status) "
                    + "VALUE(?, ?, ?, ?)";

            PreparedStatement ps = con.prepareStatement(sqlst1);
            ps.setString(1, memberaccount.getUsername());
            ps.setString(2, memberaccount.getPassword());
            ps.setString(3, dateFormat.format(current));
            ps.setString(4, "active");
            
            ps.executeUpdate();
            
            //get new member account id
            Integer idMemberAccount = 0;
            
            String sqlst2 = "SELECT LAST_INSERT_ID()";
            PreparedStatement ps2 = con.prepareStatement(sqlst2);
            ResultSet rs2 = ps2.executeQuery();
            
            if (rs2.next()) {
                idMemberAccount = rs2.getInt(1);
            }
            
            //2nd insert customer
            String sqlst3 = "INSERT INTO customer(firstName, lastName, address, phone, dob, email, MemberAccount_idMemberAccount) "
                    + "VALUE(?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps3 = con.prepareStatement(sqlst3);
            ps3.setString(1, customer.getFirstName());
            ps3.setString(2, customer.getLastName());
            ps3.setString(3, customer.getAddress());
            ps3.setString(4, customer.getPhone());
            ps3.setDate(5, new java.sql.Date (customer.getDob().getTime()));
            ps3.setString(6, customer.getEmail());
            ps3.setInt(7, idMemberAccount);
            
            ps3.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public boolean updateProfile(Customer customer){
        boolean result = false;
        
        try {
            String sqlst = "UPDATE customer "
                    + "SET address = ?, "
                    + "phone = ?, "
                    + "email = ? "
                    + "WHERE idCustomer = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            
            ps.setString(1, customer.getAddress());
            ps.setString(2, customer.getPhone());
            ps.setString(3, customer.getEmail());
            ps.setInt(4, customer.getIdCustomer());
            System.out.println(sqlst);
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
    
    public boolean updatePassword(Memberaccount memberaccount) {
        boolean result = false;
        
        try {
            String sqlst = "UPDATE memberaccount "
                    + "SET password = ? "
                    + "WHERE idMemberAccount = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            
            ps.setString(1, memberaccount.getPassword());
            ps.setInt(2, memberaccount.getIdMemberAccount());
            
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
}
